﻿using BrnMall.Manager.Domain.Rander;
using PetaPoco;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BrnMall.Manager.Manager.Radar
{
    public static class ActionRecordManager
    {
        static string TableName = "crm_ActionRecord";
        /// <summary>
        /// 添加雷达
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool Add(ActionRecord model)
        {
            using (var db = new QDMallDatabase())
            {
                db.Insert(model);
            }
            return model.Id > 0;
        }

        /// <summary>
        /// 时间倒序并查看某些人看你且计数
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<ActionRecord> GetRadarTimeList(int staffId, int page, int pageSize = 10)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ActionRecord>(page, pageSize, string.Format("select * from {0} where staffId={1} order by id desc", TableName, staffId));
            }
        }


        public static List<ActionRecord> GetRadarTimeDetail(int staffId,int actionType, int page, int pageSize = 10)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ActionRecord>(page, pageSize, string.Format("select * from {0} where staffId={1} and actionType={2} order by id desc", TableName, staffId, actionType));
            }
        }




        /// <summary>
        ///  操作次数汇总, 按天统计
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<ActionTotalView> GetDayTotal(string staffIds, string startTime, string endTime)
        {
            var sql = new StringBuilder();
            sql.Append("select DateNumber as [date],count(1) total from crm_actionrecord where 1=1 ");
            if (!string.IsNullOrEmpty(staffIds))
            {
                if (staffIds.Contains(","))
                    sql.AppendFormat(" and staffId in({0})", staffIds);
                else
                    sql.AppendFormat(" and staffId in({0})", staffIds);
            }

            if (!string.IsNullOrEmpty(startTime))
                sql.AppendFormat(" and DateNumber between {0} and {1}", Convert.ToDateTime(startTime).ToString("yyyyMMdd"), Convert.ToDateTime(endTime).ToString("yyyyMMdd"));

            sql.Append("group by DateNumber");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ActionTotalView>(sql.ToString());
            }
        }

        /// <summary>
        /// 按类型统计次数
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetGroupbyActionType(int Uid, int staffId)
        {
            var sql = string.Format("select actionType,count(1) as total from {0} where uid={1} and staffId={2} group by actionType", TableName, Uid, staffId);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }
        /// <summary>
        /// 按类型统计次数
        /// </summary>
        /// <param name="staffId"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetGroupbyActionType(int staffId)
        {
            var sql = string.Format("select actionType,count(1) as total from {0} where staffId={1} group by actionType", TableName, staffId);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }


        /// <summary>
        /// 按天统计次数
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetGroupbyDate(int Uid, int staffId, string startTime, string endTime)
        {
            var sql = string.Format("select DateNumber,count(1) as Total from {0} where uid={1} and staffId={2} and  DateNumber between {3} and {4} group by DateNumber", TableName, Uid, staffId, startTime, endTime);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }
        /// <summary>
        /// 按天统计次数
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetGroupbyDate(string staffIds, string startTime, string endTime)
        {
            var sql = string.Format("select DateNumber,count(1) as Total from {0} where staffId in ({1}) and  DateNumber between {2} and {3} group by DateNumber", TableName, staffIds, startTime, endTime);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }

        /// <summary>
        /// 按天统计次数
        /// </summary>
        /// <param name="staffId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetGroupbyDate(int staffId, string startTime, string endTime)
        {
            var sql = string.Format("select DateNumber,count(1) as Total from {0} where staffId ={1} and  DateNumber between {2} and {3} group by DateNumber", TableName, staffId, startTime, endTime);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }

        /// <summary>
        /// 雷达人物栏，对应使用的次数
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static List<ClientTotalInfo> GetGroupTotalByUid(int staffId, string startTime, string endTime, int page, int pageSize)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("select uid,count(1) as total from {0} where staffId={1}", TableName, staffId);

            if (!string.IsNullOrEmpty(startTime))
                sql.AppendFormat(" and DateNumber>={0}", Convert.ToDateTime(startTime).ToString("yyyyMMdd"));

            if (!string.IsNullOrEmpty(endTime))
                sql.AppendFormat(" and DateNumber<{0}", Convert.ToDateTime(endTime).ToString("yyyyMMdd"));

            sql.Append(" group by uid");
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ClientTotalInfo>(page, pageSize, sql.ToString());
            }
        }

        /// <summary>
        /// 根据使用人查找各个分类的使用情况
        /// </summary>
        /// <param name="Uid"></param>
        /// <param name="staffId"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetStaffIdByActionType(int Uid, int staffId, string startTime, string endTime)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("select actionType,COUNT(1) as total from {0} where 1=1", TableName, Uid);
            if (Uid > 0)
                sql.AppendFormat(" and Uid={0}", Uid);
            if (staffId > 0)
                sql.AppendFormat(" and staffId={0}", staffId);
            if (!string.IsNullOrEmpty(startTime))
                sql.AppendFormat(" and DateNumber>={0}", Convert.ToDateTime(startTime).ToString("yyyyMMdd"));

            if (!string.IsNullOrEmpty(endTime))
                sql.AppendFormat(" and DateNumber<{0}", Convert.ToDateTime(endTime).ToString("yyyyMMdd"));
            sql.Append(" group by actionType");
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql.ToString());
            }
        }

        /// <summary>
        /// 操作次数分组汇总
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<ActionTotalView> GetActionTotal(string staffIds, string startTime, string endTime)
        {
            var sql = new StringBuilder();
            sql.Append("select actionType,count(1) as total  from crm_actionRecord ");
            sql.AppendFormat(" where staffId in ({0})", staffIds);

            if (!string.IsNullOrEmpty(startTime))
                sql.AppendFormat(" and DateNumber between {0} and {1}", Convert.ToDateTime(startTime).ToString("yyyyMMdd"), Convert.ToDateTime(endTime).ToString("yyyyMMdd"));

            sql.Append("group by actionType");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ActionTotalView>(sql.ToString());
            }
        }

        /// <summary>
        /// 指定操作的统计
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="actionTypes"></param>
        /// <returns></returns>
        public static List<RadarNumInfo> GetTotalGroupByStaffId(string staffIds, string actionTypes)
        {
            var sql = string.Format("select staffid,actionType,count(1) as total from crm_ActionRecord where staffid in ({0}) and actionType in ({1}) group by staffid,actionType", staffIds, actionTypes);
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<RadarNumInfo>(sql);
            }
        }

        public static Page<ActionRecord> GetList(int uid, int staffId, int page, int pageSize = 15)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Page<ActionRecord>(page, pageSize, $"select * from crm_ActionRecord where uid={uid} and staffid={staffId} order by Addtime desc");
            }
        }
        
        public static int Update(int StaffId, int NewStaffId) {
            using (var db = new QDMallDatabase())
            {
                return db.Execute(string.Format("update crm_ActionRecord set StaffId={0} where StaffId={1}", NewStaffId, StaffId));
            }
        }
    }
}
